package com.yenlien.traceyun.control.dao;

import com.yenlien.traceyun.control.entity.BankInfo;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * 支行信息
 */
public interface BankInfoDao extends BaseJpaDao<BankInfo> {


    @Query(value = "select id,address,create_time,name,tel,pid,company_id from t_bank_info where pid=?1", nativeQuery = true)
    List<BankInfo> findByPid(String pid);   //根据pid查询

    @Query(value = "select id,address,create_time,name,pid,tel,company_id from t_bank_info where id=?1", nativeQuery = true)
    BankInfo findById(String id);

    @Query(value = "select bf.id,bf.address,bf.create_time,bf.name,bf.pid,bf.tel,bl.alias,bf.company_id," +
            "bf.name as bankName,(select count(bf.id) from t_bank_info bf inner join t_bank_level bl on bl.id=bf.pid)" +
            " as totalPage from t_bank_info bf inner join t_bank_level bl on bl.id=bf.pid" +
            " limit ?1,?2", nativeQuery = true)
    List<Map<String, Object>> findAllAndLevel(int page, int size);

    @Query(value = "select bf.id,bf.address,bf.create_time,bf.name,bf.pid,bf.company_id,bf.tel,bl.alias,bf.name as bankName " +
            " from t_bank_info bf inner join t_bank_level bl on bl.id=bf.pid where bf.id=?1", nativeQuery = true)
    List<Map<String, Object>> findById2(String id);

    @Query(value = "select id,address,create_time,name,pid,tel,company_id from t_bank_info where tel=?1", nativeQuery = true)
    BankInfo findBytel(String tel);

    @Query(value = "select id,address,create_time,name,pid,tel,company_id from t_bank_info where company_id=?1", nativeQuery = true)
    BankInfo findByCompanyId(String companyId);
}
